﻿using System;
using System.CodeDom.Compiler;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using CSW.Framework.Linq.SchemaModel;
using Microsoft.CSharp;
using Container = CSW.Framework.Linq.QueryModel.Container;
using Query = CSW.Framework.Linq.QueryModel.Query;
using Property = CSW.Framework.Linq.QueryModel.Property;

namespace CSW.Framework.Linq
{
    partial class BusinessObjectLinqBase<T>
    {
        // Static database/schema related fields
        internal static readonly T m_Database;
        internal static readonly Database m_Schema;
        internal static readonly string m_ConnectionString;

        // Dictionaries
        private static readonly Dictionary<Type, Table> m_TypeTableDictionary = new Dictionary<Type, Table>();
        private static readonly Dictionary<Type, Dictionary<string, MethodInfo>> m_TypePropertySetterDictionary = new Dictionary<Type, Dictionary<string, MethodInfo>>();
        private static readonly Dictionary<Type, Dictionary<string, MethodInfo>> m_TypePropertyGetterDictionary = new Dictionary<Type, Dictionary<string, MethodInfo>>();
        private static readonly Dictionary<Type, Dictionary<string, FieldInfoWrapper>> m_TypeFieldDictionary = new Dictionary<Type, Dictionary<string, FieldInfoWrapper>>();
        private static readonly Dictionary<Table, InsertQuery> m_TableInsertQuery = new Dictionary<Table, InsertQuery>();
        private static readonly Dictionary<Table, UpdateQuery> m_TableUpdateQuery = new Dictionary<Table, UpdateQuery>();
        private static readonly Dictionary<TableColumn, TableProperty> m_SettableTableProperty = new Dictionary<TableColumn, TableProperty>();

        // Dictionary Lockers
        private static readonly object m_TypeTableDictionaryLocker = new object();
        private static readonly object m_TypePropertySetterDictionaryLocker = new object();
        private static readonly object m_TypePropertyGetterDictionaryLocker = new object();
        private static readonly object m_TypeFieldSetterDictionaryLocker = new object();
        private static readonly object m_TableInsertQueryLocker = new object();
        private static readonly object m_TableUpdateQueryLocker = new object();
        private static readonly object m_SettableTablePropertyLocker = new object();

        /// <summary>
        /// Initializes the <see cref="BusinessObjectLinq&lt;T&gt;"/> class.
        /// </summary>
        static BusinessObjectLinqBase()
        {
            m_Database = new T();
            m_Schema = m_Database.Schema;
            m_ConnectionString = m_Database.ConnectionString;
        }

        #region <<< Public Methods >>>

        /// <summary>
        /// Gets the database schema model.
        /// </summary>
        /// <returns>The database schema model.</returns>
        public static Database GetSchema()
        {
            if (m_Schema == null)
                throw new InvalidOperationException("Schema has not finished loading.");

            return m_Schema;
        }

        /// <summary>
        /// Gets the connection string.
        /// </summary>
        /// <returns>The connection string.</returns>
        public static string GetConnectionString()
        {
            return m_ConnectionString;
        }

        /// <summary>
        /// Gets the delete statement SQL for a specified business object.
        /// </summary>
        /// <param name="item">The item.</param>
        /// <returns>The delete statement SQL.</returns>
        public static string GetDeleteStatement(BusinessObjectLinq<T> item)
        {
            if (item == null)
                throw new ArgumentNullException("item", "Parameter 'item' cannot be null.");

            Table table = item.GetTable();
            Dictionary<string, MethodInfo> propertyGetter = GetPropertyGetterDictionary(item.GetType(), table);

            if (item.IsNew)
                throw new Exception("Cannot delete an object not saved to the database.");

            StringBuilder deleteStatement;
            if (m_Schema.Provider == "SQL Server")
                deleteStatement = new StringBuilder(string.Format("DELETE FROM [{0}].[{1}] WHERE ", table.Schema, table.TableName));
            else if (m_Schema.Provider == "SQL Server CE")
                deleteStatement = new StringBuilder(string.Format("DELETE FROM [{0}] WHERE ", table.TableName));
            else
                throw new Exception("Unknown provider");

            StringBuilder whereStatement = new StringBuilder();
            for (int i = 0; i < table.PrimaryKey.Length; i++)
            {
                TableColumn tableColumn = table.PrimaryKey[i];

                if (whereStatement.Length != 0)
                    whereStatement.Append(" AND");

                TableProperty property = GetPrimitiveTableProperty(table, tableColumn);
                object value = propertyGetter[property.PropertyName].Invoke(item, null);
                if (value == null)
                    throw new Exception(string.Format("Property '{0}' cannot be null.", property.PropertyName));

                whereStatement.AppendFormat("[{0}] = '{1}'", tableColumn.ColumnName, value.ToString().Replace("'", "''"));
            }

            deleteStatement.Append(whereStatement);

            return deleteStatement.ToString();
        }

        /// <summary>
        /// Gets the delete criteria in SQL for a specified business object.
        /// </summary>
        /// <param name="item">The item.</param>
        /// <returns>The delete criteria in SQL.</returns>
        public static string GetDeleteCriteria(BusinessObjectLinq<T> item)
        {
            if (item == null)
                throw new ArgumentNullException("item", "Parameter 'item' cannot be null.");

            Table table = item.GetTable();
            Dictionary<string, MethodInfo> propertyGetter = GetPropertyGetterDictionary(item.GetType(), table);

            if (item.IsNew)
                throw new Exception("Cannot delete an object not saved to the database.");

            StringBuilder whereStatement = new StringBuilder();
            for (int i = 0; i < table.PrimaryKey.Length; i++)
            {
                TableColumn tableColumn = table.PrimaryKey[i];

                if (whereStatement.Length != 0)
                    whereStatement.Append(" AND");

                TableProperty property = GetPrimitiveTableProperty(table, tableColumn);
                object value = propertyGetter[property.PropertyName].Invoke(item, null);
                if (value == null)
                    throw new Exception(string.Format("Property '{0}' cannot be null.", property.PropertyName));

                whereStatement.AppendFormat("[{0}] = '{1}'", tableColumn.ColumnName, value.ToString().Replace("'", "''"));
            }

            return whereStatement.ToString();
        }

        #endregion <<< Public Methods >>>

        #region <<< Private Methods >>>

        internal static TableProperty GetPrimitiveTableProperty(Table table, TableColumn column)
        {
            if (table == null)
                throw new ArgumentNullException("table", "Parameter 'table' cannot be null.");
            if (column == null)
                throw new ArgumentNullException("column", "Parameter 'column' cannot be null.");

            List<TableProperty> properties = table.Properties.Where(p => p.Column == column).ToList();
            if (properties.Count == 0)
                throw new Exception(string.Format("Column '{0}' not found in table '{1}'.", column.ColumnName, table));

            properties = properties.Where(p => p.IsPrimitiveType).ToList();
            if (properties.Count == 1)
                return properties[0];
            else if (properties.Count == 0)
                throw new Exception(string.Format("Primitive type property for column '{0}' in table '{1}' not found.", column.ColumnName, table));
            else
                throw new Exception(string.Format("{2} primitive type properties for column '{0}' in table '{1}' found.", column.ColumnName, table, properties.Count));
        }

        private static TableProperty GetSettableTableProperty(Table table, TableColumn column)
        {
            if (table == null)
                throw new ArgumentNullException("table", "Parameter 'table' cannot be null.");
            if (column == null)
                throw new ArgumentNullException("column", "Parameter 'column' cannot be null.");

            lock (m_SettableTablePropertyLocker)
            {
                TableProperty tableProperty;
                if (m_SettableTableProperty.TryGetValue(column, out tableProperty))
                {
                    return tableProperty;
                }
                else
                {
                    List<TableProperty> properties = table.Properties.Where(p => p.Column == column).ToList();
                    if (properties.Count == 1)
                    {
                        tableProperty = properties[0];
                    }
                    else if (properties.Count == 0)
                    {
                        throw new Exception(string.Format("Column '{0}' not found in table '{1}'.", column.ColumnName, table));
                    }
                    else
                    {
                        // if there's more than one property representing this column, grab the ADT version
                        properties = properties.Where(p => !p.IsPrimitiveType).ToList();
                        if (properties.Count == 1)
                            tableProperty = properties[0];
                        else if (properties.Count == 0)
                            throw new Exception(string.Format("More than one primitive type property for column '{0}' in table '{1}' found.", column.ColumnName, table));
                        else
                            throw new Exception(string.Format("{2} abstract data type properties for column '{0}' in table '{1}' found.", column.ColumnName, table, properties.Count));
                    }

                    m_SettableTableProperty.Add(column, tableProperty);
                    return tableProperty;
                }
            }
        }

        internal static void RefreshOriginalValues(IBusinessObjectLinq item)
        {
            Type tableType = item.GetType();
            Table table = GetTable(tableType);

            Dictionary<string, FieldInfoWrapper> fields = GetFieldDictionary(tableType, table);
            Dictionary<string, MethodInfo> props = GetPropertyGetterDictionary(tableType, table);
            foreach (KeyValuePair<string, FieldInfoWrapper> kvp in fields)
            {
                string propertyName = kvp.Key;
                object value = props[propertyName].Invoke(item, null);
                kvp.Value.SetValue(item, value);
            }
        }

        internal static UpdateQuery GetUpdateSql(BusinessObjectLinqBase<T> businessObject)
        {
            if (businessObject == null)
                throw new ArgumentNullException("businessObject", "Parameter 'businessObject' cannot be null.");

            Table table = businessObject.GetTable();
            Type type = businessObject.GetType();

            UpdateQuery updateQuery;
            if (!m_TableUpdateQuery.TryGetValue(table, out updateQuery))
            {
                lock (m_TableUpdateQueryLocker)
                {
                    if (!m_TableUpdateQuery.TryGetValue(table, out updateQuery))
                    {
                        StringBuilder sql = new StringBuilder();
                        if (m_Schema.Provider == "SQL Server")
                            sql.AppendLine(string.Format("UPDATE [{0}].[{1}] SET", table.Schema, table.TableName));
                        else if (m_Schema.Provider == "SQL Server CE")
                            sql.AppendLine(string.Format("UPDATE [{0}] SET", table.TableName));
                        else
                            throw new Exception("Unknown provider");

                        StringBuilder sqlWhere = new StringBuilder();
                        sqlWhere.AppendLine("WHERE");

                        Dictionary<SqlParameter, string> paramList = new Dictionary<SqlParameter, string>();
                        Dictionary<SqlParameter, string> origValParamList = new Dictionary<SqlParameter, string>();

                        bool addCommaSet = false;
                        bool addCommaWhere = false;

                        for (int i = 0; i < table.Properties.Count; i++)
                        {
                            TableProperty property = table.Properties[i];
                            if (property.IsChildContainer || !property.IsPrimitiveType)
                                continue;

                            TableColumn column = property.Column;
                            //Guard.Assert(() => column != null);
                            //Guard.Assert(() => !string.IsNullOrEmpty(column.ColumnName));

                            string propertyName = property.PropertyName;

                            if (!column.IsReadOnly && !column.IsIdentity)
                            {
                                if (addCommaSet)
                                    sql.AppendLine(",");

                                string parameterName = string.Format("@{0}", column.ColumnName);
                                SqlParameter p = new SqlParameter(parameterName, column.SqlDbType);
                                paramList.Add(p, propertyName);

                                sql.Append(string.Format("[{0}] = {1}", column.ColumnName, parameterName));

                                addCommaSet = true;
                            }

                            if (addCommaWhere)
                                sqlWhere.AppendLine(" AND");

                            string origValParameterName = string.Format("@_Original_{0}", column.ColumnName);
                            sqlWhere.Append(string.Format("([{0}] = {1} OR ([{0}] IS NULL AND {1} IS NULL))", column.ColumnName, origValParameterName));
                            SqlParameter p2 = new SqlParameter(origValParameterName, column.SqlDbType);

                            origValParamList.Add(p2, propertyName);

                            addCommaWhere = true;
                        }

                        sql.AppendLine();
                        sql.Append(sqlWhere);

                        Dictionary<string, MethodInfo> getterDictionary = GetPropertyGetterDictionary(type, table);
                        Dictionary<string, FieldInfoWrapper> fieldDictionary = GetFieldDictionary(type, table);
                        updateQuery = new UpdateQuery(m_Schema.Provider, sql.ToString(), paramList, origValParamList, getterDictionary, fieldDictionary);
                        m_TableUpdateQuery.Add(table, updateQuery);
                    }
                }
            }

            return updateQuery;
        }

        /// <summary>
        /// Gets the insert SQL.
        /// </summary>
        /// <param name="businessObject">The business object.</param>
        /// <returns></returns>
        internal static InsertQuery GetInsertSql(BusinessObjectLinqBase<T> businessObject)
        {
            if (businessObject == null)
                throw new ArgumentNullException("businessObject", "Parameter 'businessObject' cannot be null.");

            Table table = businessObject.GetTable();
            Type type = businessObject.GetType();

            InsertQuery insertQuery;
            if (!m_TableInsertQuery.TryGetValue(table, out insertQuery))
            {
                lock (m_TableInsertQueryLocker)
                {
                    if (!m_TableInsertQuery.TryGetValue(table, out insertQuery))
                    {
                        StringBuilder sql = new StringBuilder();
                        if (m_Schema.Provider == "SQL Server")
                            sql.AppendLine(string.Format("INSERT INTO [{0}].[{1}] (", table.Schema, table.TableName));
                        else if (m_Schema.Provider == "SQL Server CE")
                            sql.AppendLine(string.Format("INSERT INTO [{0}] (", table.TableName));
                        else
                            throw new Exception("Unknown provider");

                        StringBuilder sqlValues = new StringBuilder();
                        sqlValues.AppendLine(") VALUES (");

                        Dictionary<SqlParameter, string> paramList = new Dictionary<SqlParameter, string>();

                        bool addComma = false;
                        bool hasIdentity = false;

                        for (int i = 0; i < table.Properties.Count; i++)
                        {
                            TableProperty property = table.Properties[i];
                            if (property.IsChildContainer || !property.IsPrimitiveType)
                                continue;

                            TableColumn column = property.Column;
                            //Guard.Assert(() => column != null);
                            //Guard.Assert(() => !string.IsNullOrEmpty(column.ColumnName));

                            if (!column.IsIdentity)
                            {
                                if (!column.IsReadOnly)
                                {
                                    if (addComma)
                                    {
                                        sql.AppendLine(",");
                                        sqlValues.AppendLine(",");
                                    }

                                    sql.Append(string.Format("[{0}]", column.ColumnName));

                                    string parameterName = string.Format("@{0}", column.ColumnName);

                                    SqlParameter p = new SqlParameter(parameterName, column.SqlDbType);
                                    paramList.Add(p, property.PropertyName);

                                    sqlValues.Append(parameterName);

                                    addComma = true;
                                }
                            }
                            else
                            {
                                hasIdentity = true;
                            }
                        }

                        sql.AppendLine();
                        sql.Append(sqlValues);
                        sql.AppendLine(");");

                        if (hasIdentity)
                        {
                            sql.AppendLine();

                            if (m_Schema.Provider == "SQL Server")
                                sql.AppendLine("SELECT SCOPE_IDENTITY();");
                            else if (m_Schema.Provider == "SQL Server CE")
                                sql.AppendLine("SELECT @@IDENTITY;");
                            else
                                throw new Exception("Unknown provider");
                        }

                        Dictionary<string, MethodInfo> getterDictionary = GetPropertyGetterDictionary(type, table);
                        insertQuery = new InsertQuery(m_Schema.Provider, sql.ToString(), hasIdentity, paramList, getterDictionary);
                        m_TableInsertQuery.Add(table, insertQuery);
                    }
                }
            }

            return insertQuery;
        }

        private class ForcedReference
        {
            public string ChildColumnName { get; set; }
            public object ParentObject { get; set; }
        }

        internal static void LoadFromDataRow(BusinessObjectLinqBase<T> item, Query dynamicQuery, DataRow dataRow)
        {
            if (item == null)
                throw new ArgumentNullException("item", "Parameter 'item' cannot be null.");
            if (dynamicQuery == null)
                throw new ArgumentNullException("dynamicQuery", "Parameter 'dynamicQuery' cannot be null.");
            if (dataRow == null)
                throw new ArgumentNullException("dataRow", "Parameter 'dataRow' cannot be null.");

            Type type = item.GetType();
            ILoadVariables loadVariables = LoadVariables<T>.Get(type, dynamicQuery);
            item.DynamicQuery = dynamicQuery;

            Table table = loadVariables.Table;
            Dictionary<string, MethodInfo> propertySetterDictionary = loadVariables.PropertySetterDictionary;
            Dictionary<string, FieldInfoWrapper> fieldDictionary = loadVariables.FieldDictionary;
            Container container = loadVariables.Container;
            IEnumerable<Property> mainTableProperties = loadVariables.MainTableProperties;
            IEnumerable<Container> virtualContainers = loadVariables.VirtualContainers;
            //Query dynamicQuery = DynamicQuery;

            item.BeginLoading();
            try
            {
                foreach (Property mainTableProperty in mainTableProperties)
                {
                    TableColumn column = mainTableProperty.TableColumn;
                    TableProperty property = GetSettableTableProperty(container.Table, column);

                    object value;
                    bool valueRetrieved = true;

                    if (column.FKID == null)
                    {
                        string dataColumnName = dynamicQuery.GetReturnedColumnName(container, column);
                        value = dataRow[dataColumnName];
                        if (value == DBNull.Value)
                            value = null;
                    }
                    else
                    {
                        Container fkContainer;
                        if (dynamicQuery.TryGetContainerLinkedTo(mainTableProperty, out fkContainer))
                        {
                            BusinessObjectLinq<T> fk = ConvertToType(fkContainer, GetFKTable(column), dataRow, dynamicQuery, null);
                            value = fk;
                        }
                        else
                        {
                            // we won't get all fk's
                            value = null;
                            valueRetrieved = false;
                        }
                    }

                    // Only use the property setter if the object has been initialized
                    if (item.PropertiesInitialized)
                    {
                        propertySetterDictionary[property.PropertyName].Invoke(item, new[] { value });
                    }

                    // Always update the "FromDb" fields
                    if (valueRetrieved)
                        fieldDictionary[property.PropertyName].SetValue(item, value);
                    else
                        fieldDictionary[property.PropertyName].ClearValue(item);

                    if (column.FKID != null)
                    {
                        property = GetPrimitiveTableProperty(container.Table, column);

                        if (valueRetrieved)
                        {
                            string dataColumnName = dynamicQuery.GetReturnedColumnName(container, column);
                            value = dataRow[dataColumnName];
                            if (value == DBNull.Value)
                                value = null;
                            fieldDictionary[property.PropertyName].SetValue(item, value);
                        }
                        else
                        {
                            fieldDictionary[property.PropertyName].ClearValue(item);
                        }
                    }
                }

                // "Virtual"/"Reverse" foreign keys
                foreach (Container referencingContainer in virtualContainers)
                {
                    string childClassName = referencingContainer.Table.FullClassName;
                    string childColumnName = referencingContainer.JoinColumn.ColumnName;

                    TableProperty property = table.Properties.Where(p => p.ChildDataType == childClassName && p.ChildColumnName == childColumnName).Single();

                    ForcedReference reference = new ForcedReference();
                    reference.ChildColumnName = childColumnName;
                    reference.ParentObject = item;

                    // bugbug
                    // When adding a "reverse" container and its properties are iterated,
                    // it considers its key to the parent table to be a new object
                    // example: T0.ID = T1.ID, where T1.ID is a UQFK, T1.ID creates a new container, should use T0

                    Table childTable = referencingContainer.Table;
                    BusinessObjectLinq<T> fk = ConvertToType(referencingContainer, childTable, dataRow, dynamicQuery, reference);
                    //BusinessObjectLinq<T> fk = ConvertToType(container, table, dataRow, dynamicQuery); does not work
                    object value = fk;

                    // Only use the property setter if the object has been initialized
                    if (item.PropertiesInitialized)
                    {
                        propertySetterDictionary[property.PropertyName].Invoke(item, new[] { value });
                    }

                    // Always update the "FromDb" fields
                    fieldDictionary[property.PropertyName].SetValue(item, value);
                }

                foreach (TableColumn pkColumn in table.PrimaryKey)
                {
                    TableProperty pkProperty = GetSettableTableProperty(table, pkColumn);
                    if (fieldDictionary[pkProperty.PropertyName].GetValue(item) == null)
                        throw new Exception("Internal Error: Primary Key is null after LoadFromDataRow.");
                }

                item.m_IsNew = false;
            }
            finally
            {
                item.EndLoading();
            }
        }

        /*private static BusinessObjectLinq<T> ConvertToType(Container container, TableColumn column, DataRow dataRow, Query dynamicQuery)
        {
            return ConvertToType(container, GetFKTable(column), dataRow, dynamicQuery);
        }

        private static BusinessObjectLinq<T> ConvertToType(Container container, Table fkTable, DataRow dataRow, Query dynamicQuery)
        {
            return ConvertToType(container, fkTable, dataRow, dynamicQuery, null);
        }*/

        /*private static BusinessObjectLinq<T> ConvertToType(Container container, TableColumn column, DataRow dataRow, Query dynamicQuery, ForcedReference reference)
        {
            return ConvertToType(container, GetFKTable(column), dataRow, dynamicQuery, reference);
        }*/

        private static BusinessObjectLinq<T> ConvertToType(Container container, Table fkTable, DataRow dataRow, Query dynamicQuery, ForcedReference reference)
        {
            Type type = m_Database.GetTableType(fkTable);
            Dictionary<string, MethodInfo> propertySetterDictionary = GetPropertySetterDictionary(type, fkTable);
            Dictionary<string, FieldInfoWrapper> fieldDictionary = GetFieldDictionary(type, fkTable);

            BusinessObjectLinq<T> tableInstance = (BusinessObjectLinq<T>)type.GetConstructor(Type.EmptyTypes).Invoke(null);
            IEnumerable<Property> dataMap = dynamicQuery.GetProperties(container);

            Table sourceTable = container.Table;

            tableInstance.BeginLoading();
            try
            {
                foreach (Property item in dataMap)
                {
                    TableProperty property = GetSettableTableProperty(sourceTable, item.TableColumn);

                    if (reference != null)
                    {
                        if (reference.ChildColumnName == property.Column.ColumnName)
                        {
                            object parentObject = reference.ParentObject;

                            if (tableInstance.PropertiesInitialized)
                                propertySetterDictionary[property.PropertyName].Invoke(tableInstance, new[] { parentObject });

                            // Always update the "FromDb" fields
                            fieldDictionary[property.PropertyName].SetValue(tableInstance, parentObject);

                            if (item.TableColumn.FKID != null)
                            {
                                property = GetPrimitiveTableProperty(sourceTable, item.TableColumn);

                                string dataColumnName = dynamicQuery.GetReturnedColumnName(container, item.TableColumn);
                                object tmpvalue = dataRow[dataColumnName];
                                if (tmpvalue == DBNull.Value)
                                    tmpvalue = null;

                                // if our PK is null, no object to create. return null.
                                if (tmpvalue == null && item.TableColumn.IsKey)
                                    return null;

                                fieldDictionary[property.PropertyName].SetValue(tableInstance, tmpvalue);
                            }

                            continue;
                        }
                    }

                    object value;

                    bool valueRetrieved = true;
                    if (item.TableColumn.FKID == null)
                    {
                        string dataColumnName = dynamicQuery.GetReturnedColumnName(container, item.TableColumn);
                        value = dataRow[dataColumnName];
                        if (value == DBNull.Value)
                        {
                            // if our PK is null, no object to create. return null.
                            if (container.Table.PrimaryKey[0] == item.TableColumn)
                                return null;

                            value = null;
                        }
                    }
                    else
                    {
                        Container fkContainer;
                        if (dynamicQuery.TryGetContainerLinkedTo(item, out fkContainer))
                        {
                            BusinessObjectLinq<T> fk = ConvertToType(fkContainer, GetFKTable(item.TableColumn), dataRow, dynamicQuery, null);
                            value = fk;
                        }
                        else
                        {
                            // we won't get all fk's
                            valueRetrieved = false;
                            value = null;
                        }
                    }

                    // Only use the property setter if the object has been initialized
                    if (tableInstance.PropertiesInitialized)
                    {
                        MethodInfo setter = propertySetterDictionary[property.PropertyName];
                        if (setter.ReturnType == typeof(sbyte?) && value is byte)
                        {
                            value = (sbyte?)(byte?)value;
                        }

                        propertySetterDictionary[property.PropertyName].Invoke(tableInstance, new[] { value });
                    }

                    // Always update the "FromDb" fields
                    if (valueRetrieved)
                        fieldDictionary[property.PropertyName].SetValue(tableInstance, value);
                    else
                        fieldDictionary[property.PropertyName].ClearValue(tableInstance);

                    if (item.TableColumn.FKID != null)
                    {
                        property = GetPrimitiveTableProperty(sourceTable, item.TableColumn);

                        if (valueRetrieved)
                        {
                            string dataColumnName = dynamicQuery.GetReturnedColumnName(container, item.TableColumn);
                            value = dataRow[dataColumnName];
                            if (value == DBNull.Value)
                                value = null;
                            fieldDictionary[property.PropertyName].SetValue(tableInstance, value);
                        }
                        else
                        {
                            fieldDictionary[property.PropertyName].ClearValue(tableInstance);
                        }
                    }

                    // TODO: no logic to populate "virtual"/"reverse" fk's here
                }

                // "Virtual"/"Reverse" foreign keys
                /*foreach (Container referencingContainer in dynamicQuery.GetVirtualContainers(container))
                {
                    // TODO: no logic to populate "virtual"/"reverse" fk's here
                }*/

                // check primary key is set
                foreach (TableColumn pkColumn in fkTable.PrimaryKey)
                {
                    TableProperty pkProperty = GetSettableTableProperty(fkTable, pkColumn);
                    if (fieldDictionary[pkProperty.PropertyName].GetValue(tableInstance) == null)
                        throw new Exception("Internal Error: Primary Key is null after ConvertToType.");
                }

                tableInstance.m_IsNew = false;
            }
            finally
            {
                tableInstance.EndLoading();
            }

            return tableInstance;
        }

        private static void ConvertToTypeCompiled(string indent, StringBuilder source, int iteration, Container container, Table fkTable, Query dynamicQuery, string parentObjectName, string referenceChildColumnName)
        {
            Type type = m_Database.GetTableType(fkTable);
            //Dictionary<string, MethodInfo> propertySetterDictionary = GetPropertySetterDictionary(type, fkTable);
            //Dictionary<string, FieldInfoWrapper> fieldDictionary = GetFieldDictionary(type, fkTable);

            string tableInstanceName = "tableInstance";
            //string tmpValueName = "tmpTmpValue";
            string valueName = "tmpValue";
            string previousValueName = "value";
            //string valueRetrievedName = "tmpValueRetrieved";
            if (iteration > 1)
            {
                tableInstanceName += iteration;
                //tmpValueName += iteration;
                valueName += iteration;
                //valueRetrievedName += iteration;

                previousValueName = "tmpValue";
                if (iteration > 2)
                {
                    previousValueName += (iteration - 1);
                }
            }

            //StringBuilder source = new StringBuilder();
            string internalsInterface = string.Format("I{0}Internals", type.Name);
            source.AppendLine(indent + "while (true)");
            source.AppendLine(indent + "{");
            source.AppendLine(string.Format(indent + "    {2} {1} = new {0}();", type.FullName, tableInstanceName, internalsInterface));
            //BusinessObjectLinq<T> tableInstance = (BusinessObjectLinq<T>)type.GetConstructor(Type.EmptyTypes).Invoke(null);
            IEnumerable<Property> dataMap = dynamicQuery.GetProperties(container);

            Table sourceTable = container.Table;

            source.AppendLine(string.Format(indent + "    {0}.BeginLoading();", tableInstanceName));
            source.AppendLine(indent + "    try");
            source.AppendLine(indent + "    {");
            source.AppendLine(string.Format(indent + "        object {0};", valueName));
            //source.AppendLine(string.Format(indent + "        object {0} = null;", tmpValueName));
            source.AppendLine();
            //source.AppendLine(string.Format("        bool {0} = true;", valueRetrievedName));

            foreach (Property item in dataMap)
            {
                bool valueRetrieved = true;

                TableProperty property = GetSettableTableProperty(sourceTable, item.TableColumn);

                if (parentObjectName != "null")
                {
                    if (referenceChildColumnName == property.Column.ColumnName)
                    {
                        //object parentObject = reference.ParentObject;

                        //source.AppendLine(string.Format(indent + "        if ({0}.PropertiesInitialized)", tableInstanceName));
                        //source.AppendLine(string.Format(indent + "            {0}.{1} = {2};", tableInstanceName, property.PropertyName, parentObjectName));

                        // Always update the "FromDb" fields
                        //fieldDictionary[property.PropertyName].SetValue(tableInstance, parentObject);
                        source.AppendLine(string.Format(indent + "        {0}.m_{1}FromDb = ({2})item;", tableInstanceName, property.PropertyName, property.DataType));
                        source.AppendLine(string.Format(indent + "        {0}.m_Is{1}LoadedFromDb = true;", tableInstanceName, property.PropertyName));

                        if (item.TableColumn.FKID != null)
                        {
                            property = GetPrimitiveTableProperty(sourceTable, item.TableColumn);

                            string dataColumnName = dynamicQuery.GetReturnedColumnName(container, item.TableColumn);

                            source.AppendLine(string.Format(indent + "        {0} = dataRow[\"{1}\"];", valueName, dataColumnName));
                            if (property.Column.IsNullable || property.Column.IsKey)
                            {
                                source.AppendLine(string.Format(indent + "        if ({0} == DBNull.Value)", valueName));
                                source.AppendLine(string.Format(indent + "            {0} = null;", valueName));
                            }

                            // if our PK is null, no object to create. return null.
                            //if (item.TableColumn.IsKey) // todo - is this the right thing to do?
                            if (property.Column.IsKey)
                            {
                                source.AppendLine(string.Format(indent + "        if ({0} == null)", valueName));
                                source.AppendLine(string.Format(indent + "        {{"));
                                source.AppendLine(string.Format(indent + "            throw new Exception();"));
                                //source.AppendLine(string.Format(indent + "            {0} = null;", tableInstanceName));
                                //source.AppendLine(string.Format(indent + "            break;"));
                                source.AppendLine(string.Format(indent + "        }}"));
                            }

                            source.AppendLine(string.Format(indent + "        {0}.m_{1}FromDb = ({3}){2};", tableInstanceName, property.PropertyName, valueName, property.DataType));
                            source.AppendLine(string.Format(indent + "        {0}.m_Is{1}LoadedFromDb = true;", tableInstanceName, property.PropertyName));
                            source.AppendLine();
                        }

                        continue;
                    }
                }

                if (item.TableColumn.FKID == null)
                {
                    string dataColumnName = dynamicQuery.GetReturnedColumnName(container, item.TableColumn);
                    source.AppendLine(string.Format(indent + "        {0} = dataRow[\"{1}\"];", valueName, dataColumnName));
                    
                    if (item.TableColumn.IsNullable || item.TableColumn.IsKey)
                    {
                        source.AppendLine(string.Format(indent + "        if ({0} == DBNull.Value)", valueName));
                        source.AppendLine(string.Format(indent + "            {0} = null;", valueName));
                    }

                    if (container.Table.PrimaryKey[0] == item.TableColumn)
                    {
                        // if our PK is null, no object to create. return null.
                        source.AppendLine(string.Format(indent + "        if ({0} == null)", valueName));
                        source.AppendLine(string.Format(indent + "        {{"));
                        source.AppendLine(string.Format(indent + "            throw new Exception();"));
                        //source.AppendLine(string.Format(indent + "            {0} = null;", tableInstanceName));
                        //source.AppendLine(string.Format(indent + "            break;"));
                        source.AppendLine(string.Format(indent + "        }}"));
                    }
                }
                else
                {
                    Container fkContainer;
                    if (dynamicQuery.TryGetContainerLinkedTo(item, out fkContainer))
                    {
                        string dataColumnName = dynamicQuery.GetReturnedColumnName(container, item.TableColumn);
                        source.AppendLine(string.Format(indent + "        //"));
                        source.AppendLine(string.Format(indent + "        // {0}", dataColumnName));
                        source.AppendLine(string.Format(indent + "        //"));
                        ConvertToTypeCompiled(indent + "        ", source, iteration + 1, fkContainer, GetFKTable(item.TableColumn), dynamicQuery, null, null);
                        //value = fk;
                    }
                    else
                    {
                        // we won't get all fk's
                        valueRetrieved = false;
                        source.AppendLine(string.Format(indent + "        {0} = null;", valueName));
                    }
                }

                // Only use the property setter if the object has been initialized
                //source.AppendLine(string.Format(indent + "        if ({0}.PropertiesInitialized)", tableInstanceName));
                //source.AppendLine(string.Format(indent + "            {0}.{1} = ({3}){2};", tableInstanceName, property.PropertyName, valueName, property.DataType));

                // Always update the "FromDb" fields
                if (valueRetrieved)
                {
                    //source.AppendLine(string.Format("if ({0})", valueRetrievedName));
                    //source.AppendLine("{");
                    source.AppendLine(string.Format(indent + "        {0}.m_{1}FromDb = ({2}){3};", tableInstanceName, property.PropertyName, property.DataType, valueName));
                    source.AppendLine(string.Format(indent + "        {0}.m_Is{1}LoadedFromDb = true;", tableInstanceName, property.PropertyName, valueName));
                }
                else
                {
                    //source.AppendLine("}");
                    //source.AppendLine("else");
                    //source.AppendLine("{");
                    source.AppendLine(string.Format(indent + "        {0}.m_{1}FromDb = null;", tableInstanceName, property.PropertyName));
                    source.AppendLine(string.Format(indent + "        {0}.m_Is{1}LoadedFromDb = false;", tableInstanceName, property.PropertyName));
                    //source.AppendLine("}");
                }
                source.AppendLine();

                if (item.TableColumn.FKID != null)
                {
                    property = GetPrimitiveTableProperty(sourceTable, item.TableColumn);

                    if (valueRetrieved)
                    {
                        //source.AppendLine(string.Format("if ({0})", valueRetrievedName));
                        //source.AppendLine("{");
                        string dataColumnName = dynamicQuery.GetReturnedColumnName(container, item.TableColumn);
                        source.AppendLine(string.Format(indent + "        {0} = dataRow[\"{1}\"];", valueName, dataColumnName));
                        source.AppendLine(string.Format(indent + "        if ({0} == DBNull.Value)", valueName));
                        source.AppendLine(string.Format(indent + "            {0} = null;", valueName));
                        //fieldDictionary[property.PropertyName].SetValue(tableInstance, value);
                        source.AppendLine(string.Format(indent + "        {0}.m_{1}FromDb = ({3}){2};", tableInstanceName, property.PropertyName, valueName, property.DataType));
                        source.AppendLine(string.Format(indent + "        {0}.m_Is{1}LoadedFromDb = false;", tableInstanceName, property.PropertyName));
                        //source.AppendLine("}");
                    }
                    else
                    {
                        //source.AppendLine("else");
                        //source.AppendLine("{");
                        //fieldDictionary[property.PropertyName].ClearValue(tableInstance);
                        source.AppendLine(string.Format(indent + "        {0}.m_{1}FromDb = null;", tableInstanceName, property.PropertyName));
                        source.AppendLine(string.Format(indent + "        {0}.m_Is{1}LoadedFromDb = false;", tableInstanceName, property.PropertyName));
                        //source.AppendLine("}");
                    }
                }

                // TODO: no logic to populate "virtual"/"reverse" fk's here
            }

            // "Virtual"/"Reverse" foreign keys
            /*foreach (Container referencingContainer in dynamicQuery.GetVirtualContainers(container))
            {
                // TODO: no logic to populate "virtual"/"reverse" fk's here
            }*/

            // check primary key is set
            foreach (TableColumn pkColumn in fkTable.PrimaryKey)
            {
                TableProperty pkProperty = GetSettableTableProperty(fkTable, pkColumn);
                source.AppendLine(string.Format(indent + "        if ({0}.{1} == null)", tableInstanceName, pkProperty.PropertyName));
                source.AppendLine(indent + "            throw new Exception(\"Internal Error: Primary Key is null after ConvertToType.\");");
                //if (fieldDictionary[pkProperty.PropertyName].GetValue(tableInstance) == null)
                //    throw new Exception("Internal Error: Primary Key is null after ConvertToType.");
            }

            source.AppendLine(string.Format(indent + "        {0}.m_IsNew = false;", tableInstanceName));
            source.AppendLine(indent + "    }");
            source.AppendLine(indent + "    finally");
            source.AppendLine(indent + "    {");
            source.AppendLine(string.Format(indent + "        {0}.EndLoading();", tableInstanceName));
            source.AppendLine(indent + "    }");
            // todo - valueName should relate to caller
            source.AppendLine();
            source.AppendLine(string.Format(indent + "    {0} = {1}; // ****", previousValueName, tableInstanceName));
            source.AppendLine(string.Format(indent + "    break;"));
            source.AppendLine(indent + "}");
            source.AppendLine();
        }

        internal static Dictionary<string, MethodInfo> GetPropertyGetterDictionary(Type tableClassType, Table table)
        {
            if (tableClassType == null)
                throw new ArgumentNullException("tableClassType", "Parameter 'tableClassType' cannot be null.");

            lock (m_TypePropertyGetterDictionaryLocker)
            {
                Dictionary<string, MethodInfo> propertyGetterDictionary;
                if (!m_TypePropertyGetterDictionary.TryGetValue(tableClassType, out propertyGetterDictionary))
                {
                    const BindingFlags bf = BindingFlags.Instance | BindingFlags.Public;

                    propertyGetterDictionary = new Dictionary<string, MethodInfo>();
                    foreach (TableProperty property in table.Properties)
                    {
                        if (property.IsChildList)
                            continue;

                        MethodInfo getter = tableClassType.GetProperty(property.PropertyName, bf).GetGetMethod();
                        propertyGetterDictionary.Add(property.PropertyName, getter);
                    }

                    m_TypePropertyGetterDictionary.Add(tableClassType, propertyGetterDictionary);
                }

                return propertyGetterDictionary;
            }
        }

        internal static Dictionary<string, MethodInfo> GetPropertySetterDictionary(Type tableClassType, Table table)
        {
            if (tableClassType == null)
                throw new ArgumentNullException("tableClassType", "Parameter 'tableClassType' cannot be null.");

            lock (m_TypePropertySetterDictionaryLocker)
            {
                Dictionary<string, MethodInfo> propertySetterDictionary;
                if (!m_TypePropertySetterDictionary.TryGetValue(tableClassType, out propertySetterDictionary))
                {
                    const BindingFlags bf = BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic;

                    propertySetterDictionary = new Dictionary<string, MethodInfo>();
                    foreach (TableProperty property in table.Properties)
                    {
                        if (property.IsChildList)
                            continue;

                        bool missingSetOK = false;
                        if (property.Column != null)
                        {
                            // ReSharper disable AccessToModifiedClosure
                            TableProperty linkedProperty = table.Properties.Where(p => p.Column == property.Column && p != property && !p.IsPrimitiveType).SingleOrDefault();
                            if (linkedProperty != null)
                                missingSetOK = true;
                            // ReSharper restore AccessToModifiedClosure
                        }

                        PropertyInfo propertyInfo = tableClassType.GetProperty(property.PropertyName, bf);
                        if (propertyInfo == null)
                            throw new Exception(string.Format("Property '{0}' not found.", property.PropertyName));
                        MethodInfo setter = propertyInfo.GetSetMethod(true);

                        if (setter == null && !missingSetOK)
                            throw new Exception(string.Format("Property '{0}' setter not found.", property.PropertyName));

                        if (setter != null)
                            propertySetterDictionary.Add(property.PropertyName, setter);
                    }

                    m_TypePropertySetterDictionary.Add(tableClassType, propertySetterDictionary);
                }

                return propertySetterDictionary;
            }
        }

        internal static Dictionary<string, FieldInfoWrapper> GetFieldDictionary(Type tableClassType, Table table)
        {
            if (tableClassType == null)
                throw new ArgumentNullException("tableClassType", "Parameter 'tableClassType' cannot be null.");

            lock (m_TypeFieldSetterDictionaryLocker)
            {
                Dictionary<string, FieldInfoWrapper> fieldInfoDictionary;
                if (!m_TypeFieldDictionary.TryGetValue(tableClassType, out fieldInfoDictionary))
                {
                    fieldInfoDictionary = new Dictionary<string, FieldInfoWrapper>();
                    foreach (TableProperty property in table.Properties)
                    {
                        if (property.IsChildList)
                            continue;

                        fieldInfoDictionary.Add(property.PropertyName, new FieldInfoWrapper(tableClassType, property.PropertyName));
                    }

                    m_TypeFieldDictionary.Add(tableClassType, fieldInfoDictionary);
                }

                return fieldInfoDictionary;
            }
        }

        private static Table GetFKTable(TableColumn column)
        {
            if (column.FKTable != null)
                return column.FKTable;

            throw new Exception("Table not found.");
        }

        /// <summary>
        /// Gets the <see cref="Table"/> associated with a specified type.
        /// </summary>
        /// <param name="tableClassType">Type of the table class.</param>
        /// <returns>The <see cref="Table"/> associated with the specified type.</returns>
        public static Table GetTable(Type tableClassType)
        {
            if (tableClassType == null)
                throw new ArgumentNullException("tableClassType", "Parameter 'tableClassType' cannot be null.");

            lock (m_TypeTableDictionaryLocker)
            {
                Table table;
                if (m_TypeTableDictionary.TryGetValue(tableClassType, out table))
                {
                    return table;
                }
                else
                {
                    foreach (Table testTable in m_Schema.Tables)
                    {
                        if (testTable.FullClassName == tableClassType.FullName)
                        {
                            m_TypeTableDictionary.Add(tableClassType, testTable);
                            return testTable;
                        }
                    }
                }
            }

            throw new Exception(string.Format("Table for type '{0}' not found in schema.", tableClassType.FullName));
        }

        /*private static void CheckUniqueness(ErrorMessage errorMessage, IBusinessObject obj, IDictionary<string, MethodInfo> propDict, IDictionary<string, FieldInfoWrapper> fieldDict, IEnumerable<TableProperty> pkProperties, IEnumerable<TableProperty> uqProperties, StringBuilder originalSql)
        {
            if (obj.ObjectState == BindableObjectState.Pristine)
                return;

            StringBuilder sql = new StringBuilder(originalSql.ToString());

            // TODO: handle other providers
            DbConnection conn = m_Database.GetThreadConnection();
            List<DbParameter> param = new List<DbParameter>();

            // don't skip on new records, also values aren't loaded from db so exception will be thrown on field getter
            bool valuesAreSame = !obj.IsNew;

            int i = 0;
            foreach (TableProperty property in uqProperties)
            {
                if (i > 0)
                    sql.Append(" AND ");

                sql.AppendFormat(" T1.[{0}] ", property.Column.ColumnName);

                object value = propDict[property.PropertyName].Invoke(obj, null);

                if (valuesAreSame)
                {
                    object origValue = fieldDict[property.PropertyName].GetValue(obj);

                    if (value != origValue)
                        valuesAreSame = false;
                }

                if (value == null)
                {
                    sql.Append(" IS NULL ");
                }
                else
                {
                    string paramName = string.Format("@p{0}", param.Count);

                    sql.AppendFormat(" = {0} ", paramName);
                    DbParameter p = DataHelper.GetDbParameter(conn, paramName, property.Column.SqlDbType, value);

                    param.Add(p);
                }

                i++;
            }

            if (!obj.IsNew)
            {
                if (valuesAreSame)
                {
                    return;
                }

                sql.Append(" AND (");
                i = 0;
                foreach (TableProperty pk in pkProperties)
                {
                    if (i > 0)
                        sql.Append(" OR ");

                    string paramName = string.Format("@p{0}", param.Count);
                    sql.AppendFormat(" T1.[{0}] != {1} ", pk.Column.ColumnName, paramName);

                    object value = propDict[pk.PropertyName].Invoke(obj, null);
                    DbParameter p = DataHelper.GetDbParameter(conn, paramName, pk.Column.SqlDbType, value);

                    param.Add(p);

                    i++;
                }
                sql.Append(")");
            }

            DataTable dt;
            try
            {
                dt = DataHelper.GetDataTable(conn, sql.ToString(), param.ToArray());
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                throw;
            }

            if (dt.Rows.Count != 0)
            {
                string errMsg = string.Empty;
                i = 0;
                foreach (TableProperty uqProperty in uqProperties)
                {
                    if (i > 0)
                        errMsg += ", ";

                    errMsg += string.Format("{0} '{1}'", StringConverter.GetPrettyName(uqProperty.PropertyName), propDict[uqProperty.PropertyName].Invoke(obj, null));

                    i++;
                }

                errMsg = errMsg.Trim() + " already exists in ";

                DataRow dr = dt.Rows[0];
                i = 0;
                foreach (TableProperty pk in pkProperties)
                {
                    if (i > 0)
                        errMsg += ", ";

                    string pkColumnName = pk.Column.ColumnName;
                    errMsg += string.Format("{0} {1}", StringConverter.GetPrettyName(pkColumnName), dr[pkColumnName]);

                    i++;
                }
                errMsg += ".";

                errorMessage.Append(errMsg);
            }
        }*/

        #endregion <<< Private Methods >>>
    }
}
